package dao.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.Date;
import model.PublicHolidayDTO;
import model.search.PublicHolidaySearchDTO;
import dao.DAOException;
import dao.PublicHolidayDAO;

public class PublicHolidayDAOImpl extends BaseDAO implements PublicHolidayDAO {
    public PublicHolidayDAOImpl() {
        super();
    }
    
    private ArrayList<PublicHolidayDTO> getList(PreparedStatement ps) throws SQLException {
        ArrayList<PublicHolidayDTO> list = new ArrayList<PublicHolidayDTO>();
        
        PublicHolidayDTO data = null;
        
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            data = new PublicHolidayDTO();
            data.setStartdate(rs.getDate("StartDate"));
            data.setEnddate(rs.getDate("EndDate"));
            data.setDescription(rs.getString("Description"));
            list.add(data);
        }
        rs.close();
        
        return list;
    }
    
    public ArrayList<PublicHolidayDTO> getAll() throws DAOException {
        openConnection(false);

        ArrayList<PublicHolidayDTO> list = new ArrayList<PublicHolidayDTO>();
        try {
            String sql = "select * from Holiday";
            PreparedStatement prep = this.getPreparedStatement(sql);
            list = getList(prep);
            prep.close();
        } catch (Exception e) {
            String error = "Error on Get All Holiday. Nested Exception is: "
                    + e;
            super.printLog(error, Level.SEVERE);
            throw new DAOException(error);
        } finally {
            closeConnection();
        }
        return list;
    }
    
    public PublicHolidayDTO getPublicHoliday(Date startDate) throws DAOException {
        openConnection(false);

        PublicHolidayDTO data = null;

        try {
            String sql = "select * from Holiday where StartDate = ? ";
            PreparedStatement preq = getPreparedStatement(sql);
            preq.setDate(1, new java.sql.Date(startDate.getTime()));
            ResultSet rs = preq.executeQuery();
            if (rs.next()) {
                data = new PublicHolidayDTO();
                data.setStartdate(rs.getDate("StartDate"));
                data.setEnddate(rs.getDate("EndDate"));
                data.setDescription(rs.getString("Description"));
            }

            rs.close();
            preq.close();
        } catch (Exception e) {
            String error = "Error on GetHoliday. Nested Exception is: " + e;
            super.printLog(error, Level.SEVERE);
            throw new DAOException(error);
        } finally {
            closeConnection();
        }

        return data;
    }
    
    private String getWhereFilter(PublicHolidaySearchDTO src) {
        String where = "";
        
        if ( src.getStartDateFrom() != null && src.getStartDateTo() != null ) {
            where = " WHERE ";
            if ( src.getStartDateFrom() == null ) {
                where += " ( StartDate < ? ) ";
            } else if ( src.getStartDateTo() == null ) {
                where += " ( StartDate > ? ) ";
            } else {
                where += " ( StartDate > ? AND StartDate < ? ) ";
            }
        } 
        
        if ( src.getEndDateFrom() != null && src.getEndDateTo() != null ) {
            if (where.isEmpty()) where = " WHERE "; else where += " AND ";
            if ( src.getEndDateFrom() == null ) {
                where += " ( EndDate < ? ) ";
            } else if ( src.getStartDateTo() == null ) {
                where += " ( EndDate > ? ) ";
            } else {
                where += " ( EndDate > ? AND EndDate < ? ) ";
            }
        }
        
        if ( src.getDescription() != null && src.getDescription().trim().equalsIgnoreCase("") ) { 
            if (where.isEmpty()) where = " WHERE "; else where += " AND ";
            where += " Description LIKE ? ";
        }
        
        return where;
    }
    
    private ArrayList getParameters(PublicHolidaySearchDTO src) {
        ArrayList params = new ArrayList();
        
        if ( src.getStartDateFrom() != null && src.getStartDateTo() != null ) {
            if ( src.getStartDateFrom() == null ) {
                params.add( new java.sql.Date(src.getStartDateTo().getTime()) );
            } else if ( src.getStartDateTo() == null ) {
                params.add( new java.sql.Date(src.getStartDateFrom().getTime()) );
            } else {
                params.add( new java.sql.Date(src.getStartDateFrom().getTime()) );
                params.add( new java.sql.Date(src.getStartDateTo().getTime()) );
            }
        } 
        
        if ( src.getEndDateFrom() != null && src.getEndDateTo() != null ) {
            if ( src.getEndDateFrom() == null ) {
                params.add( new java.sql.Date(src.getEndDateTo().getTime()) );
            } else if ( src.getStartDateTo() == null ) {
                params.add( new java.sql.Date(src.getEndDateFrom().getTime()) );
            } else {
                params.add( new java.sql.Date(src.getEndDateFrom().getTime()) );
                params.add( new java.sql.Date(src.getEndDateTo().getTime()) );
            }
        }
        
        if ( src.getDescription() != null && src.getDescription().trim().equalsIgnoreCase("") ) { 
            params.add("%" + src.getDescription() + "%");
        }
        
        return params;
    }
    
    public ArrayList<PublicHolidayDTO> findPublicHolidayByCriteria(PublicHolidaySearchDTO src)
            throws DAOException {
        openConnection();

        ArrayList<PublicHolidayDTO> list = new ArrayList<PublicHolidayDTO>();
        try {
            String sql = "";
            String where = "";
            ArrayList params = new ArrayList();

            sql = "SELECT * FROM Holiday ";
            where = getWhereFilter(src);
            params = getParameters(src);

            PreparedStatement prep = getPreparedStatement(sql + " " + where);
            for (int i = 1; i <= params.size(); i++) {
                prep.setObject(i, params.get(i - 1));
            }

            printLog("Executing FindPublicHolidayByCriteria: " + sql + where, Level.INFO);
            
            list = getList(prep);
            
            prep.close();
        } catch (Exception e) {
            String error = "Error FindPublicHolidayByCriteria. Nested Exception is: " + e;
            printLog(error, Level.SEVERE);
            throw new DAOException(error);
        } finally {
            closeConnection();
        }

        return list;
    }
    
    @Override
    public void insertPublicHoliday(PublicHolidayDTO data) throws DAOException {
        openConnection(false);

        try {
            String sql = "INSERT INTO Holiday " + 
                         " ( StartDate, EndDate, Description " +
                         " ) VALUES ( ?, ?, ? ) ";
            PreparedStatement prep = getPreparedStatement(sql);
            prep.setDate(1, new java.sql.Date( data.getStartdate().getTime() ) ) ;
            prep.setDate(2, new java.sql.Date( data.getEnddate().getTime() ) ) ;
            prep.setString(3, data.getDescription());
            prep.executeUpdate();

            printLog("Executing Insert: " + sql, Level.INFO);

            commit();
            prep.close();
        } catch (Exception e) {
            String error = "Error Inserting Public Holiday. Nested Exception is: " + e;
            printLog(error, Level.SEVERE);
            throw new DAOException(error);
        } finally {
            closeConnection();
        }
    }

    @Override
    public void updatePublicHoliday(PublicHolidayDTO data) throws DAOException {
        openConnection(false);

        try {
            String sql = "UPDATE Holiday SET EndDate = ?, Description = ? "
                    + " WHERE (StartDate = ? ) ";            
            PreparedStatement prep = getPreparedStatement(sql);
            prep.setDate(1, new java.sql.Date( data.getEnddate().getTime() ) ) ;
            prep.setString(2, data.getDescription());
            prep.setDate(3, new java.sql.Date( data.getStartdate().getTime() ) ) ;
            prep.executeUpdate();

            commit();
            prep.close();
        } catch (Exception e) {
            String error = "Error Updating Public Holiday. Nested Exception is: " + e;
            printLog(error, Level.SEVERE);
            throw new DAOException(error);
        } finally {
            closeConnection();
        }
    }
    
    @Override
    public void deletePublicHoliday(PublicHolidayDTO data) throws DAOException {
            openConnection(false);
        
        try {
            String sql = "DELETE FROM Holiday WHERE (StartDate = ?)";
            PreparedStatement prep = getPreparedStatement(sql);
            prep.setDate(1, new java.sql.Date( data.getStartdate().getTime() ) );

            prep.executeUpdate();

            commit();
            prep.close();
        } catch (Exception e) {
            String error = "Error Deleting Public Holiday. Nested Exception is: " + e;
            printLog(error, Level.SEVERE);
            throw new DAOException(error);
        } finally {
            closeConnection();
        }
    }
}